#将季节从content分出来
from posixpath import split
import pymysql
from time import sleep
import pandas as pd
import os
from  datetime import datetime
class season:
    def __init__(self):
        self.host='192.168.0.113'
        self.port=3306
        self.user='coname'
        self.passwd='xZS6p4LDcKCwn4Yb'
        self.charset='utf8'
        self.db='co'

    def open_mysql(self):#打开数据库
        try:
            self.conn = pymysql.connect(host=self.host,port=self.port,user=self.user,passwd=self.passwd,db=self.db,charset=self.charset)
            self.cursor=self.conn.cursor()
            print("连接数据库成功!")
            return self.conn,self.cursor
        except Exception as e:
            print("连接数据库失败>>>",e)

    def close_mysql(self,conn,cursor):#关闭数据库
        try:
            cursor.close()
            sleep(1)
            conn.close()
            print("关闭数据库成功!")
        except Exception as e:
            print("关闭数据失败！>>>",e)

    def output_data(self):#取出mysql数据
        conn,cursor = self.open_mysql()
        try:
            cursor.execute("select srname from co_agriculture_fruit")
            fruit_list = cursor.fetchall()
            print("取出数据成功!")
            return fruit_list
        except Exception as e:
            print("取出数据失败>>>",e)
        self.close_mysql(conn,cursor)

    def update_data(self,srname,season):#改变值
        conn,cursor = self.open_mysql()
        try:
            cursor.execute('update co_agriculture_fruit set season="{}" where srname="{}"'.format(str(season),srname))
            conn.commit()
            print("更改季节成功!")
        except Exception as e:
            print("改变季节出现问题！>>>",e)
        self.close_mysql(conn,cursor)

    def output_excel(self):#取出excel数据
        fruitSeason = pd.read_excel(os.path.abspath(os.path.join(os.getcwd(),'.'))+'\\水果季节.xlsx',sheet_name="Sheet1")
        nums = fruitSeason.shape[0]
        for srname in self.output_data():
            for i in range(0,nums,1):
                frutlist = fruitSeason.loc[i,"水果"]
                frutlists = frutlist.split('、')
                if srname[0] in frutlists:
                    self.update_data(srname[0],fruitSeason.loc[i,'季节'])
   
    def he(self):
       self.output_excel()

    def run(self):
        self.he()

if  __name__ == "__main__":
    season().run()